﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace MyDBHelper
{
    public class SQLHelper
    {
        private static string connString = "server=.;database=MyCloud;uid=sa;pwd=123456";

        /// <summary>
        /// 获取结果集的第一行第一列的结果
        /// </summary>
        /// <param name="sqlText">要执行的检索语句</param>
        /// <param name="paramss">检索语句中的参数列表</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sqlText, params SqlParameter[] paramss)
        {
            using (SqlConnection sqlCnn = new SqlConnection(connString))
            {
                using (SqlCommand sqlCmm = sqlCnn.CreateCommand())
                {
                    sqlCmm.CommandText = sqlText;
                    fillParameter(paramss, sqlCmm);
                    sqlCnn.Open();
                    return sqlCmm.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 填充Command对象的参数列表
        /// </summary>
        /// <param name="paramss">参数列表</param>
        /// <param name="sqlCmm">command对象</param>
        private static void fillParameter(SqlParameter[] paramss, SqlCommand sqlCmm)
        {
            foreach (SqlParameter param in paramss)
            {
                sqlCmm.Parameters.Add(param);
            }
        }

        /// <summary>
        /// 获取检索结果集，返回DataTable
        /// </summary>
        /// <param name="sqlText">要执行的检索语句</param>
        /// <param name="paramss">检索语句中的参数列表</param>
        /// <returns></returns>
        public static DataTable ExecuteTable(string sqlText, params SqlParameter[] paramss)
        {
            using (SqlConnection sqlcnn = new SqlConnection(connString))
            {
                using (SqlCommand sqlCmm = sqlcnn.CreateCommand())
                {
                    sqlCmm.CommandText = sqlText;
                    fillParameter(paramss, sqlCmm);
                    sqlcnn.Open();
                    using (SqlDataReader reader = sqlCmm.ExecuteReader())
                    {
                        DataTable dt = new DataTable();
                        dt.Load(reader);
                        return dt;
                    }
                }
            }
        }

        /// <summary>
        /// 执行对数据库的增、删、改操作，并返回数据库中受影响的行数
        /// </summary>
        /// <param name="sqlText">要执行的insert、update、delete语句</param>
        /// <param name="paramss">要执行的语句中的参数列表</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sqlText, params SqlParameter[] paramss)
        {
            using (SqlConnection sqlCnn = new SqlConnection(connString))
            {
                using (SqlCommand sqlCmm = sqlCnn.CreateCommand())
                {
                    sqlCmm.CommandText = sqlText;
                    fillParameter(paramss, sqlCmm);
                    sqlCnn.Open();
                    return sqlCmm.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行对数据库的增、删、改操作，并返回数据库中受影响的行数
        /// </summary>
        /// <param name="sqlText">要执行的insert、update、delete语句或存储过程</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paramss">要执行的语句中的参数列表</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sqlText, CommandType commandType, params SqlParameter[] paramss)
        {
            using (SqlConnection sqlCnn = new SqlConnection(connString))
            {
                using (SqlCommand sqlCmm = sqlCnn.CreateCommand())
                {
                    sqlCmm.CommandType = commandType;
                    sqlCmm.CommandText = sqlText;
                    fillParameter(paramss, sqlCmm);
                    sqlCnn.Open();
                    return sqlCmm.ExecuteNonQuery();
                }
            }
        }
    }
}
